﻿-- ================================================
-- Template generated from Template Explorer using:
-- Create Procedure (New Menu).SQL
--
-- Use the Specify Values for Template Parameters 
-- command (Ctrl-Shift-M) to fill in the parameter 
-- values below.
--
-- This block of comments will not be included in
-- the definition of the procedure.
-- ================================================
USE [WarehouseManagement]
GO

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:		AnhDQ
-- Create date: 04/03/2011
-- Description:	This stored procedure use to get an Employee record
-- =============================================
ALTER PROCEDURE [SearchPartner]
(
@PartnerCode VARCHAR(50) = NULL, 
@CompanyName NVARCHAR(50) = NULL,
@Address NVARCHAR(50) = NULL,
@IsSupplier BIT = FALSE,
@IsDelete BIT = 0
)
AS
BEGIN
	
	DECLARE @SearchPartnerQuery NVARCHAR(MAX) = 
		'
		SELECT 
			p.PartnerID
			, p.PartnerCode
			, p.CompanyName
			, p.[Description]
			, p.[Address]
			, p.Email
			, p.Fax
			, p.MobilePhone
			, p.OfficePhone
			, p.Rating
			, p.IsDelete
			, p.IsSupplier
	FROM 
			[Partner] p 
	
		WHERE 1=1'
	
	-- Chuoi dieu kien IsDelete 
	DECLARE @IsDeleteConditionTemplate NVARCHAR(MAX) = 
		N'
			AND p.IsDelete = @IsDelete'
	DECLARE @IsDeletePlace NVARCHAR(MAX) = N'@IsDelete'
	DECLARE @IsDeleteCondition NVARCHAR(MAX) = ''
	
	IF (@IsDelete IS NOT NULL)
		SET @IsDeleteCondition = REPLACE ( @IsDeleteConditionTemplate , @IsDeletePlace , @IsDelete )

	-- Chuoi dieu kien IsSupplier
	DECLARE @IsSupplierConditionTemplate NVARCHAR(MAX) = 
		N'
			AND p.IsSupplier = @isSupplier'
	DECLARE @IsSupplierPlace NVARCHAR(MAX) = N'@IsSupplier'
	DECLARE @IsSupplierCondition NVARCHAR(MAX) = ''
	
	IF (@IsSupplier IS NOT NULL)
		SET @IsSupplierCondition = REPLACE ( @IsSupplierConditionTemplate , @IsSupplierPlace , @IsSupplier )

	-- Chuoi dieu kien PartnerCode 
	DECLARE @PartnerCodeConditionTemplate NVARCHAR(MAX) = 
		N'
			AND p.PartnerCode LIKE ''%@PartnerCode%'' '
	DECLARE @PartnerCodePlace NVARCHAR(MAX) = N'@PartnerCode'
	DECLARE @PartnerCodeCondition NVARCHAR(MAX) = ''

	IF (@PartnerCode IS NOT NULL)
		SET @PartnerCodeCondition = REPLACE ( @PartnerCodeConditionTemplate , @PartnerCodePlace , @PartnerCode )

	-- Chuoi dieu kien CompanyName
	DECLARE @CompanyNameConditionTemplate NVARCHAR(MAX) = 
		N'
			AND p.CompanyName LIKE ''%@CompanyName%'' '
	DECLARE @CompanyNamePlace NVARCHAR(MAX) = N'@CompanyName'
	DECLARE @CompanyNameCondition NVARCHAR(MAX) = ''

	IF (@CompanyName IS NOT NULL)
		SET @CompanyNameCondition = REPLACE ( @CompanyNameConditionTemplate , @CompanyNamePlace , @CompanyName )

	-- Chuoi dieu kien Address
	DECLARE @AddressConditionTemplate NVARCHAR(MAX) = 
		N'
			AND p.Address LIKE ''%@Address%'' '
	DECLARE @AddressPlace NVARCHAR(MAX) = N'@Address'
	DECLARE @AddressCondition NVARCHAR(MAX) = ''

	IF (@Address IS NOT NULL)
		SET @AddressCondition = REPLACE ( @AddressConditionTemplate , @AddressPlace , @Address )

	-- Thuc hien query

	SELECT @SearchPartnerQuery += @IsDeleteCondition + @IsSupplierCondition + @PartnerCodeCondition
								+ @CompanyNameCondition + @AddressCondition

	PRINT @SearchPartnerQuery

	EXEC sp_sqlexec @p1 = @SearchPartnerQuery
		
END

/*
USE WarehouseManagement
GO

select * from Partner
select * from Contact
EXEC [SearchPartner] @PartnerCode = 's', @IsSupplier = 1
*/